﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using NRemedy;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class User_X : Basic, IUser_X
    {
        private const string selectSiteIDByName = "SELECT a.ID,b.SiteID,s.Alias SiteAlias FROM user_x a INNER JOIN usersite b ON a.ID=b.userID " + "INNER JOIN sitebranch s ON b.siteid=s.id WHERE a.name=:name";
        private const string selectAllSiteCount = "select count(*) from sitebranch";
        private const string selectAllUserCount = "select count(*) from User_X";
        private const string selectGetSiteBranchByID = "select s.* from user_x a inner join usersite b on b.userid=a.ID inner join sitebranch s on b.siteid=s.ID where a.ID=:ID";
        private const string sqlUpdateRoleByUserIDDUpdate = "update  user_x set department=:department,position=:position,disabled=:disabled,IsAdmin=:IsAdmin,IsLeader=:IsLeader,AccessUS=:AccessUS,AccessITSM=:AccessITSM,AccessGJ=:AccessGJ,PeopleNo=:PeopleNo where id=:userID";
        private const string sqlUpdateRoleByUserIDDelete = "delete from usersite where userid=:userID";
        private const string sqlUpdateRoleByUserIDInsert = "insert into usersite (userid,siteid) values(:userID,:siteID)";

        private const string sqlUpdateRole_GJByUserIDDelete = "delete from usersite_gj where userid=:userID";
        private const string sqlUpdateRole_GJByUserIDInsert = "insert into usersite_gj (userid,siteid) values(:userID,:siteID)";

        public List<UserAndSiteModel> GetSiteIDByName(string name)
        {
            return ModelHelper<UserAndSiteModel>.GetModel_By_DirectSql(selectSiteIDByName, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="name",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=20,Value=name}
                 }, oda);
        }


        public Decimal GetCountOfTheAllSite()
        {
            return ModelHelper<User_X>.GetModelCount_By_DirectSql(selectAllSiteCount, null, oda);
        }


        public User_XModel GetUser_XByName(string name)
        {
            List<QueryParameter> attParam = new List<QueryParameter>();
            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Name",
                OracleDbColumnType = OracleDbType.NVarchar2,
                OracleDbColumnSize = 20,
                OracleDbColumnValue = name.ToLower(),
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });
            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Disabled",
                OracleDbColumnType = OracleDbType.Decimal,
                OracleDbColumnValue = 0,
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });
            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, null, null, null, oda, null).FirstOrDefault();
        }

        public List<User_XModel> GetListPageAndSortUser_X(out Decimal totalRecords, int pageSize = -1, int pageIndex = -1,
            string sortName = "DisplayName", string sortDirection = "Ascending"/*Ascending,Descending*/, string name = "", string displayname = "")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sortName, sortDirection);
            List<QueryParameter> attParam = new List<QueryParameter>();
            string sqlStr = "select count(*) from User_X where 1=1 and Disabled=0 ";
            List<OracleParameter> param = new List<OracleParameter>();
            if (!string.IsNullOrEmpty(name))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Name",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 20,
                    OracleDbColumnValue = name.ToLower(),
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where
                });
                sqlStr += " and Name like :Name ";
                param.Add(new OracleParameter("Name", OracleDbType.NVarchar2, "%" + name + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(displayname))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "DisplayName",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 20,
                    OracleDbColumnValue = displayname,
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where
                });
                sqlStr += " and DisplayName like :DisplayName ";
                param.Add(new OracleParameter("DisplayName", OracleDbType.NVarchar2, "%" + displayname + "%", ParameterDirection.Input));
            }

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Disabled",
                OracleDbColumnType = OracleDbType.Decimal,
                OracleDbColumnValue = 0,
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });

            totalRecords = ModelHelper<User_XModel>.GetModelCount_By_DirectSql(sqlStr, param, oda);

            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda, null);
        }

        #region 重载GetListPageAndSortUser_X方法 +List<User_XModel> GetListPageAndSortUser_X
        /// <summary>
        /// 根据公司名称获取User_X
        /// </summary>
        /// <param name="totalRecords"></param>
        /// <param name="companyName">公司名称</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="sortName"></param>
        /// <param name="sortDirection"></param>
        /// <param name="name"></param>
        /// <param name="displayname"></param>
        /// <returns></returns>
        public List<User_XModel> GetListPageAndSortUser_X(out Decimal totalRecords, string companyName, int pageSize = -1, int pageIndex = -1,
           string sortName = "DisplayName", string sortDirection = "Ascending"/*Ascending,Descending*/, string name = "", string displayname = "")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sortName, sortDirection);
            List<QueryParameter> attParam = new List<QueryParameter>();
            string sqlStr = "select count(*) from User_X where 1=1 and Disabled=0 ";
            List<OracleParameter> param = new List<OracleParameter>();
            if (!string.IsNullOrEmpty(companyName))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Company",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 20,
                    OracleDbColumnValue = companyName,
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where
                });
                sqlStr += " and Company like :Company ";
                param.Add(new OracleParameter("Company", OracleDbType.NVarchar2, "%" + companyName + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(name))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Name",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 20,
                    OracleDbColumnValue = name.ToLower(),
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where
                });
                sqlStr += " and Name like :Name ";
                param.Add(new OracleParameter("Name", OracleDbType.NVarchar2, "%" + name + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(displayname))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "DisplayName",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    OracleDbColumnSize = 20,
                    OracleDbColumnValue = displayname,
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where
                });
                sqlStr += " and DisplayName like :DisplayName ";
                param.Add(new OracleParameter("DisplayName", OracleDbType.NVarchar2, "%" + displayname + "%", ParameterDirection.Input));
            }

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "Disabled",
                OracleDbColumnType = OracleDbType.Decimal,
                OracleDbColumnValue = 0,
                QueryOperator = QueryOperatorType.equal,
                PredicateType = PredicateType.where
            });

            totalRecords = ModelHelper<User_XModel>.GetModelCount_By_DirectSql(sqlStr, param, oda);

            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda, null);
        }
        #endregion

        public List<SiteBranchModel> GetSiteBranchByID(decimal ID)
        {
            return ModelHelper<SiteBranchModel>.GetModel_By_DirectSql(selectGetSiteBranchByID, new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="ID",OracleDbType=OracleDbType.Decimal,Value=ID}
                 }, oda);
        }

        public SiteBranchModel GetSiteBranchBySiteID(decimal ID)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            return ModelHelper<SiteBranchModel>.GetModel_By_QueryParams(attParam, null, null, null, oda, null).FirstOrDefault();
        }


        public User_XModel GetUserByID(decimal ID)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, null, null, null, oda, null).FirstOrDefault();

        }

        public SFUser_XModel GetUserByIDSF(decimal ID)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            var model = ModelHelper<SFUser_XModel>.GetModel_By_QueryParams(attParam, null, null, null, oda, null,
             new List<string>() { "ID", "UserIdentify", "Name", "DisplayName", "Department", "Position", "Disabled", "IsAdmin", "CreateTime", "LastModifiedTime", "Company", "IsLeader", "AccessUS", "AccessITSM", "AccessGJ", "PeopleNo" }).FirstOrDefault();

            using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString))
            {
                conn.Open();
                OracleCommand command2 = conn.CreateCommand();
                command2.CommandType = CommandType.Text;
                command2.CommandText = @"select phone,email   from accountcopy where accountid = :accountid";
                var accountid = new OracleParameter("accountid", OracleDbType.NVarchar2, 100);
                accountid.Value = model.Name;

                command2.Parameters.Add(accountid);

                using (var reader = command2.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["phone"] != DBNull.Value) model.Mobile = (string)reader["phone"];
                        if (reader["email"] != DBNull.Value) model.Email = (string)reader["email"];
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 根据条件查询员工
        /// </summary>
        /// <returns></returns>
        public List<User_XModel> GetUserListByQueryParameter(out decimal totalCount, QueryString query, Nullable<int> pageIndex, Nullable<int> pageSize, string sort = "DisplayName", string order = "Descending")
        {
            List<QueryParameter> attParam = new List<QueryParameter>();

            attParam.Add(new QueryParameter()
            {
                OracleDbColumnName = "disabled",
                OracleDbColumnType = OracleDbType.Decimal,
                QueryOperator = QueryOperatorType.notEqual,
                PredicateType = PredicateType.where,
                OracleDbColumnValue = 1
            });

            if (!string.IsNullOrWhiteSpace(query.Name))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "DisplayName",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where,
                    OracleDbColumnValue = query.Name
                });
            }

            if (!string.IsNullOrWhiteSpace(query.Company))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Company",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    QueryOperator = QueryOperatorType.like,
                    PredicateType = PredicateType.where,
                    OracleDbColumnValue = query.Company
                });
            }

            if (!string.IsNullOrWhiteSpace(query.Department))
            {
                attParam.Add(new QueryParameter()
                {
                    OracleDbColumnName = "Department",
                    OracleDbColumnType = OracleDbType.NVarchar2,
                    QueryOperator = QueryOperatorType.equal,
                    PredicateType = PredicateType.where,
                    OracleDbColumnValue = query.Department
                });
            }

            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);
            totalCount = ModelHelper<User_XModel>.GetModelCount_By_QueryParams(attParam, oda);

            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, pageSize, pageIndex, oda);
        }

        public string UpdateRoleByUserID(SFUser_XModel model, List<decimal> roles_US, List<decimal> roles_GJ)
        {
            try
            {
                using (OracleConnection conSelf = new OracleConnection(ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString))
                {
                    conSelf.Open();
                    if (model.Disabled == 1)
                    {
                        OracleCommand delcmd = new OracleCommand();
                        this.PrepareCommand(delcmd, conSelf, null, CommandType.Text, "delete from accountcopy where accountid = :accountid", new List<OracleParameter>(){
                    new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name.ToLower(),ParameterDirection.Input)
                    });
                        delcmd.ExecuteNonQuery();

                    }
                    else
                    {
                        OracleCommand updatecmd = new OracleCommand();
                        this.PrepareCommand(updatecmd, conSelf, null, CommandType.Text, "update AccountCopy set dept=:department,position=:position,phone=:phone,email=:email where accountid=:accountid", new List<OracleParameter>() 
                        { 
                            new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                            new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                            new OracleParameter("phone", OracleDbType.NVarchar2, 100, model.Mobile, ParameterDirection.Input),
                            new OracleParameter("email", OracleDbType.NVarchar2, 100, model.Email, ParameterDirection.Input),
                            new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name.ToLower(),ParameterDirection.Input)
                        });
                        updatecmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return "自服务 系统错误，请重新点击保存，或联系管理员。（" + e.Message + ")";
            }
            try
            {
                string ARServer = ConfigurationManager.AppSettings["ARServer"];
                string ARUid = ConfigurationManager.AppSettings["ARUid"];
                string ARPwd = ConfigurationManager.AppSettings["ARPwd"];
                ARLoginContext context = new ARLoginContext(ARServer, ARUid, ARPwd);
                ARProxy<CTMPeople> ar = new ARProxy<CTMPeople>(context);
                var entryList = ar.GetEntryByQuery(string.Format("\'4\'=\"{0}\"", model.Name.ToLower()));
                if (entryList.Count > 0)
                {
                    var entry = entryList[0];
                    if (model.Disabled == 1)
                    {
                        entry.Status = CTMPeople.Status_Enum.Delete;
                        ar.SetEntry(entry);
                        ar.DeleteEntry(entry);

                    }
                    else
                    {
                        if (!string.IsNullOrWhiteSpace(model.Department)) entry.Department = model.Department;
                        if (!string.IsNullOrWhiteSpace(model.Position)) entry.JobTitle = model.Position;
                        if (!string.IsNullOrWhiteSpace(model.Mobile)) entry.PhoneNumberBusiness = model.Mobile;
                        if (!string.IsNullOrWhiteSpace(model.Email)) entry.InternetEmail = model.Email;

                        ar.SetEntry(entry);
                    }

                }
            }
            catch (Exception e)
            {
                return "ITSM 系统错误，请重新点击保存，或联系管理员。（" + e.Message + ")";
            }


            try
            {

                if (model.Disabled == 1)
                {
                    using (var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString))
                    {
                        conn.Open();
                        MySqlCommand command = new MySqlCommand();
                        command.Connection = conn;
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "delete_personbyname";//name of procdure
                        command.Parameters.Add(new MySqlParameter("PersonName", model.Name.ToLower()));
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return "认证服务 系统错误，请重新点击保存，或联系管理员。(" + e.Message + ")";
            }


            OracleTransaction trans = oda.BeginTransaction();
            try
            {
                oda.ExecuteScalar(sqlUpdateRoleByUserIDDUpdate, new List<OracleParameter>()
                {
                    new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                    new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                    new OracleParameter("disabled", OracleDbType.Decimal,  model.Disabled, ParameterDirection.Input),
                    new OracleParameter(){ ParameterName="IsAdmin",OracleDbType=OracleDbType.Decimal,Value=model.IsAdmin },
                    new OracleParameter(){ ParameterName="IsLeader",OracleDbType=OracleDbType.Decimal,Value=model.IsLeader },
                    new OracleParameter(){ ParameterName="AccessUS",OracleDbType=OracleDbType.Decimal,Value=model.AccessUS },
                    new OracleParameter(){ ParameterName="AccessITSM",OracleDbType=OracleDbType.Decimal,Value=model.AccessITSM},
                    new OracleParameter(){ ParameterName="AccessGJ",OracleDbType=OracleDbType.Decimal,Value=model.AccessGJ },
                    new OracleParameter(){ ParameterName="userID",OracleDbType=OracleDbType.Decimal,Value=model.ID },
                    new OracleParameter(){ ParameterName="PeopleNo",OracleDbType=OracleDbType.NVarchar2,Value=model.PeopleNo }

                });
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                return "集中展现 系统错误，请重新点击保存，或联系管理员。（" + e.Message + ")";
            }
            return string.Empty;
        }

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2)
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }

        public int ChangePassword(string login, string pass)
        {
            int status = 1;


            MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString);
            try
            {
                conn.Open();
                MySqlCommand command2 = conn.CreateCommand();
                command2.CommandType = CommandType.Text;
                command2.CommandText = @" update persons p set password=@password  where p.name=@name";
                var passP = new MySqlParameter("password", MySqlDbType.String, 100);
                passP.Value = pass;
                var nameP = new MySqlParameter("name", MySqlDbType.String, 100);
                nameP.Value = login;

                command2.Parameters.Add(passP);
                command2.Parameters.Add(nameP);

                command2.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                status = 0;
            }
            finally
            {
                if (conn != null) conn.Close();
            }


            return status;
        }
        //------------------修改-------------------------------------
        #region 更新UserSite表 +int UpdateUserSiteTable(SFUser_XModel model)

        /// <summary>
        /// 更新UserSite表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateUserSiteTable(SFUser_XModel model)
        {
            int status = 0;
            OracleConnection conn = null;
            try
            {
                conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString);
                SiteBranch siteb = new SiteBranch();
                int siteID = siteb.GetSiteBranchIDByUserID(Convert.ToInt32(model.ID));
                if (siteID > 0)
                {
                    oda.ExecuteScalar(sqlUpdateRoleByUserIDInsert, new List<OracleParameter>()
                    {
                        new OracleParameter()
                        {
                            ParameterName = "userID",
                            OracleDbType = OracleDbType.Decimal,
                            Value = model.ID
                        },
                        new OracleParameter()
                        {
                            ParameterName = "siteID",
                            OracleDbType = OracleDbType.Decimal,
                            Value = siteID
                        }
                    });
                    status = 1; //表示更新成功
                }
                else
                {
                    status = -1;//代表跟新失败，无法获取siteID
                }

            }
            catch (Exception e)
            {
                status = -2;//代表跟新失败，数据库执行异常

            }
            finally
            {
                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return status;
        }
        #endregion

        #region 删除UserSite中间表中的一条数据 +int DeleteUserSiteInfo(SFUser_XModel model)
        /// <summary>
        /// 删除UserSite中间表中的一条数据
        /// </summary>
        /// <param name="model">SFUser_XModel的实例</param>
        /// <returns>是否删除成功</returns>
        public int DeleteUserSiteInfo(SFUser_XModel model)
        {
            int status = 0;
            int modelID = -1;
            if (model != null)
            {
                modelID = Convert.ToInt32(model.ID);
                int result = oda.ExecuteCommand(sqlUpdateRoleByUserIDDelete, new List<OracleParameter>()
                {   
                    new OracleParameter()
                    {
                        ParameterName="userID",
                        OracleDbType = OracleDbType.Decimal,
                        Value = modelID
                    }
                });
                if (result > 0)
                {
                    status = 1;//代表删除成功
                }
                else
                {
                    status = -1;//代表删除失败
                }
            }
            return status;
        }
        #endregion

        #region 创建新用户 +int CreateNewUser(SFUser_XModel model, out string resultStr)
        /// <summary>
        /// 创建新用户
        /// </summary>
        /// <param name="modelExt">SFUser_XExtensionModel 实例</param>
        /// <param name="resultStr"></param>
        /// <returns></returns>
        public int CreateNewUser(SFUser_XModel model, out string resultStr)
        {
            int status = 0;

            OracleConnection conn = null;
            OracleCommand cmd1 = null;
            OracleCommand insertcmd = null;
            try
            {
                conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString);
                //查询用户信息是否已存在
                string selectStr1 = "select ID from USER_X where NAME = :NAME";
                cmd1 = conn.CreateCommand();
                List<OracleParameter> cmdParms = new List<OracleParameter>();
                cmdParms.Add(new OracleParameter("NAME", OracleDbType.NVarchar2, model.Name.ToLower(), ParameterDirection.Input));
                this.PrepareCommand(cmd1, conn, null, CommandType.Text, selectStr1, cmdParms);
                int userid = Convert.ToInt32(cmd1.ExecuteScalar());
                //如果已存在，那么更新该数据
                if (userid > 0)
                {
                    string updateStr = "update USER_X set DISPLAYNAME = :DISPLAYNAME, COMPANY = :COMPANY, DEPARTMENT = :DEPARTMENT, POSITION = :POSITION ,LASTMODIFIEDTIME=:LASTMODIFIEDTIME,DISABLED=:DISABLED,PeopleNo=:PeopleNo where NAME = :NAME";
                    oda.ExecuteScalar(updateStr, new List<OracleParameter>(){
                        new OracleParameter("DISPLAYNAME", OracleDbType.NVarchar2, 100, (model.DisplayName == null ? "" : model.DisplayName), ParameterDirection.Input),
                        new OracleParameter("COMPANY", OracleDbType.NVarchar2, 100, (model.Company == null ? "" : model.Company), ParameterDirection.Input),
                        new OracleParameter("DEPARTMENT", OracleDbType.NVarchar2, 100, (model.Department == null ? "" : model.Department), ParameterDirection.Input),
                        new OracleParameter("POSITION", OracleDbType.NVarchar2, 100, (model.Position == null ? "" : model.Position), ParameterDirection.Input),
                        new OracleParameter("NAME", OracleDbType.NVarchar2, 100, model.Name.ToLower(), ParameterDirection.Input),
                        new OracleParameter("LASTMODIFIEDTIME", OracleDbType.Date, DateTime.Now, ParameterDirection.Input),
                        new OracleParameter("DISABLED", OracleDbType.Int32, 0, ParameterDirection.Input),
                    new OracleParameter("PeopleNo", OracleDbType.NVarchar2, 100, (model.PeopleNo == null ? "" : model.PeopleNo), ParameterDirection.Input)});
                }
                //如果不存在，那么插入该数据
                else
                {
                    string insertStr = "insert into USER_X (NAME, DISPLAYNAME, COMPANY, DEPARTMENT, POSITION,DISABLED,LASTMODIFIEDTIME,PeopleNo) values ( :NAME, :DISPLAYNAME, :COMPANY, :DEPARTMENT, :POSITION,:DISABLED,:LASTMODIFIEDTIME,:PeopleNo)";
                    List<OracleParameter> insertParms = new List<OracleParameter>();
                    insertParms.Add(new OracleParameter("DISPLAYNAME", OracleDbType.NVarchar2, 100, (model.DisplayName == null ? "" : model.DisplayName), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("COMPANY", OracleDbType.NVarchar2, 100, (model.Company == null ? "" : model.Company), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("DEPARTMENT", OracleDbType.NVarchar2, 100, (model.Department == null ? "" : model.Department), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("POSITION", OracleDbType.NVarchar2, 100, (model.Position == null ? "" : model.Position), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("NAME", OracleDbType.NVarchar2, 100, model.Name.ToLower(), ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("LASTMODIFIEDTIME", OracleDbType.Date, DateTime.Now, ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("DISABLED", OracleDbType.Int32, 0, ParameterDirection.Input));
                    insertParms.Add(new OracleParameter("PeopleNo", OracleDbType.NVarchar2, 100, (model.PeopleNo == null ? "" : model.PeopleNo), ParameterDirection.Input));

                    insertcmd = conn.CreateCommand();
                    this.PrepareCommand(insertcmd, conn, null, CommandType.Text, insertStr, insertParms);
                    insertcmd.ExecuteNonQuery();
                    userid = Convert.ToInt32(cmd1.ExecuteScalar());
                }
                if (model.IsRelevance)
                {
                    SiteBranch siteb = new SiteBranch();
                    SiteBranchModel site = siteb.GetSiteBranchByName(model.Company);
                    oda.ExecuteScalar(sqlUpdateRoleByUserIDInsert, new List<OracleParameter>(){
                            new OracleParameter(){ ParameterName="userID",OracleDbType=OracleDbType.Decimal,Value=userid },
                            new OracleParameter(){ ParameterName="siteID",OracleDbType=OracleDbType.Decimal,Value=site.ID }});
                }
                resultStr = "";
            }
            catch (Exception e)
            {
                status = 1;
                resultStr = e.Message;
            }
            finally
            {
                if (cmd1 != null)
                {
                    cmd1.Dispose();
                }
                if (insertcmd != null)
                {
                    insertcmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Dispose();
                }
            }
            return status;
        }
        #endregion

        public int Create(SFUser_XModel model, out string resultStr)
        {
            int status = 0;

            status = CreateNewUser(model, out resultStr);

            using (OracleConnection conSelf = new OracleConnection(ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString))
            {

                conSelf.Open();
                try
                {
                    //查询用户信息是否已存在
                    string selectStr = "select count(*) from accountcopy where accountid = :accountid";
                    List<OracleParameter> cmdParms = new List<OracleParameter>();
                    cmdParms.Add(new OracleParameter("accountid", OracleDbType.NVarchar2, model.Name.ToLower(), ParameterDirection.Input));
                    OracleCommand cmd = new OracleCommand();
                    this.PrepareCommand(cmd, conSelf, null, CommandType.Text, selectStr, cmdParms);
                    int selectcount = Convert.ToInt32(cmd.ExecuteScalar());
                    //如果已存在，那么更新该数据
                    if (selectcount > 0)
                    {
                        OracleCommand updatecmd = new OracleCommand();
                        this.PrepareCommand(updatecmd, conSelf, null, CommandType.Text, "update accountcopy set username = :username, company = :company, dept = :department, position = :position, email = :email,phone=:phone where accountid = :accountid", new List<OracleParameter>()
                        {
                            new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                            new OracleParameter("username", OracleDbType.NVarchar2, 100, model.DisplayName, ParameterDirection.Input),
                            new OracleParameter("company", OracleDbType.NVarchar2, 100, model.Company, ParameterDirection.Input),
                            new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                            new OracleParameter("phone", OracleDbType.NVarchar2, 100, model.Mobile, ParameterDirection.Input),
                            new OracleParameter("email", OracleDbType.NVarchar2, 100, model.Email, ParameterDirection.Input),
                            new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name.ToLower(),ParameterDirection.Input)
                       
                        });
                        updatecmd.ExecuteNonQuery();
                    }
                    //如果不存在，那么插入该数据
                    else
                    {
                        OracleCommand insertcmd = new OracleCommand();
                        this.PrepareCommand(insertcmd, conSelf, null, CommandType.Text, "insert into accountcopy (accountid, username, company, dept, position, email,phone) values ( :accountid, :username, :company, :department, :position, :email,:phone)",
                            new List<OracleParameter>()
                        {
                            new OracleParameter("department", OracleDbType.NVarchar2, 100, model.Department, ParameterDirection.Input),
                            new OracleParameter("username", OracleDbType.NVarchar2, 100, model.DisplayName, ParameterDirection.Input),
                            new OracleParameter("company", OracleDbType.NVarchar2, 100, model.Company, ParameterDirection.Input),
                            new OracleParameter("position", OracleDbType.NVarchar2, 100, model.Position, ParameterDirection.Input),
                            new OracleParameter("phone", OracleDbType.NVarchar2, 100, model.Mobile, ParameterDirection.Input),
                            new OracleParameter("email", OracleDbType.NVarchar2, 100, model.Email, ParameterDirection.Input),
                            new OracleParameter("accountid", OracleDbType.NVarchar2, 100, model.Name.ToLower(),ParameterDirection.Input)
                        });
                        insertcmd.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    status = 2;
                    resultStr = e.Message;
                }
            }


            try
            {
                string ARServer = ConfigurationManager.AppSettings["ARServer"];
                string ARUid = ConfigurationManager.AppSettings["ARUid"];
                string ARPwd = ConfigurationManager.AppSettings["ARPwd"];
                ARLoginContext context = new ARLoginContext(ARServer, ARUid, ARPwd);
                ARProxy<CTMPeople> ar = new ARProxy<CTMPeople>(context, new ARServerCreate());
                //ARProxy<CTMPeople> ar = new ARProxy<CTMPeople>(context);
                var entryList = ar.GetEntryByQuery(string.Format("\'4\'=\"{0}\"", model.Name.ToLower()));
                CTMPeople entry = null;
                if (entryList.Count > 0)
                {
                    entry = entryList[0];
                    entry.Department = model.Department;
                    entry.JobTitle = model.Position;
                    entry.PhoneNumberBusiness = model.Mobile;
                    entry.InternetEmail = model.Email;
                    ar.SetEntry(entry);
                }
                else
                {
                    entry = new CTMPeople
                    {
                        Assigned_To = model.Name.ToLower(),
                        //电话需确定
                        Company = model.Company,
                        //Site值需确定
                        //Site = "",需要AR存在这个Site才能添加
                        //组织没有填写部门
                        //Organization = employee.DEPT,  需要AR存在这个组织才能添加
                        Department = model.Department,
                        FirstName = model.DisplayName.Substring(1),
                        LastName = model.DisplayName.Substring(0, 1),
                        FullName = model.DisplayName,
                        InternetEmail = model.Email,
                        JobTitle = model.Position,
                        Short_Description = ".",
                        Submitter = "Demo",
                        PhoneNumberBusiness = "###",
                        ClientType = CTMPeople.ClientType_Enum.office,
                        z1DPassword = "User@123",
                        Organization = model.Company,                            //only 1 means enabled
                        Status = CTMPeople.Status_Enum.Enabled
                    };
                    ar.CreateEntry(entry);
                }
            }
            catch (Exception e)
            {
                status = 3;
                resultStr = e.Message;
            }


            using (var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString))
            {

                try
                {
                    conn.Open();
                    //var temp = conn.CreateCommand();
                    //temp.CommandType = CommandType.Text;
                    //temp.CommandText = "SET NAMES UTF8";
                    //temp.ExecuteNonQuery();
                    //MySqlCommand sqlSQL = conn.CreateCommand();
                    //sqlSQL.CommandType = CommandType.Text;
                    //sqlSQL.CommandText = "select orgcode from openldap.institutes where name=@name ";//like '@name%'";
                    //var pas = new MySqlParameter("name", MySqlDbType.String, 100);
                    //pas.Value = "福建省烟草专卖局(公司)本部"; //model.Company;
                    //sqlSQL.Parameters.Add(pas);
                    //string orgcode = string.Empty;
                    //using (var reader11 = sqlSQL.ExecuteReader())
                    //{
                    //    while (reader11.Read())
                    //    {
                    //        orgcode = (string)reader11["orgcode"];
                    //    }
                    //}

                    Dictionary<string, int> orgcodes = new Dictionary<string, int>()
                                {
                                    {"泉州市烟草专卖局", 10350501 },
                                    {"中国烟草福建进出口公司", 88880229      },
                                    {"福州市烟草专卖局", 10350101 },
                                    {"三明市烟草专卖局", 10350401 },
                                    {"龙岩市烟草专卖局", 10350801 },
                                    {"福建武夷烟叶有限公司", 88880227 },
                                    {"福建省烟草专卖局", 88880218  },
                                    {"漳州市烟草专卖局", 10350601 },
                                    {"南平市烟草专卖局", 10350701 },
                                    {"莆田市烟草专卖局", 10350301 },
                                    {"福建烟草海晟投资管理有限公司", 99990229   },
                                    {"宁德市烟草专卖局", 10350901 },
                                    {"厦门市烟草专卖局", 10350201 },
                                    {"三明金叶复烤有限公司", 88880228   }
                                };


                    MySqlCommand command = new MySqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "create_person";//name of procdure
                    command.Parameters.Add(new MySqlParameter("personCN", model.Name.ToLower()));
                    command.Parameters.Add(new MySqlParameter("personCode", Guid.NewGuid().ToString()));
                    command.Parameters.Add(new MySqlParameter("personuid", model.Name.ToLower()));
                    command.Parameters.Add(new MySqlParameter("personmail", null));
                    command.Parameters.Add(new MySqlParameter("parentCode", orgcodes[model.Company]));
                    command.Parameters.Add(new MySqlParameter("personPwd", model.Password));//pwd maybe encrypted
                    MySqlParameter param = new MySqlParameter("@entryid", model.ID);
                    param.Direction = ParameterDirection.Output;
                    command.Parameters.Add(param);
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    resultStr = e.Message;
                    status = 4;
                }
            }

            resultStr = string.Empty;
            return status;
        }


        public bool Exist(string loginName)
        {

            bool exists = false;


            MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MysqlConStr"].ConnectionString);
            try
            {
                conn.Open();
                MySqlCommand command2 = conn.CreateCommand();
                command2.CommandType = CommandType.Text;
                command2.CommandText = @" select count(*) from  persons p   where p.name=@name";
                var nameP = new MySqlParameter("name", MySqlDbType.String, 100);
                nameP.Value = loginName;

                command2.Parameters.Add(nameP);

                long count = (long)command2.ExecuteScalar();
                if (count >= 1) exists = true;
            }
            catch (Exception e)
            {
                exists = true;
            }
            finally
            {
                if (conn != null) conn.Close();
            }


            return exists;
        }


        public List<SupportGroupCompany> GetGroupsByCompany(string Company, string type)
        {
            StringBuilder sql = new StringBuilder();
            sql = sql.Append("select unique Support_Group_Name,Company from ctm_support_group where Company=:name and status=1 ");
            switch (type)
            {
                case "事件":
                    sql = sql.Append("and (support_organization='事件支持组' or support_organization='公用组') ");
                    break;
                case "变更":
                    sql = sql.Append("and (support_organization='变更支持组') and (Support_Group_Name not like '%变更委员会%') ");
                    break;
                case "问题":
                    sql = sql.Append("and (support_organization='问题支持组' or support_organization='公用组') ");
                    break;
                default:
                    sql = sql.Append("and (Support_Group_Name not like '%变更委员会%') ");
                    break;
            }
            sql = sql.Append(" order by Support_Group_Name");
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return ModelHelper<SupportGroupCompany>.GetModel_By_DirectSql(sql.ToString()
                    , new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="name",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=50,Value=Company}
                 },
                    conitsm);
            }
        }

        public List<SupportGroupCompany> GetPeople(string Company, string Group, string type)
        {
            StringBuilder sql = new StringBuilder();
            sql = sql.Append("select unique Company,Support_Group_Name,Login_ID,Full_Name from ctm_support_group_assoc_lookup where Company=:company and support_group_name=:groupname and assignment_availability!=1 ");
            switch (type)
            {
                case "事件":
                    sql = sql.Append("and (support_organization='事件支持组' or support_organization='公用组') ");
                    break;
                case "变更":
                    sql = sql.Append("and (support_organization='变更支持组') ");
                    break;
                case "问题":
                    sql = sql.Append("and (support_organization='问题支持组' or support_organization='公用组') ");
                    break;
                default:
                    break;
            }
            sql = sql.Append(" order by Full_Name");
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return ModelHelper<SupportGroupCompany>.GetModel_By_DirectSql(sql.ToString(), new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="company",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=50,Value=Company},
                     new OracleParameter(){ ParameterName="groupname",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=50,Value=Group}
                 },
                                conitsm);
            }
        }

        public List<SupportGroupCompany> GetPeoples(string Company, string type)
        {
            StringBuilder sql = new StringBuilder();
            sql = sql.Append("select distinct Full_Name, Login_ID from ctm_support_group_assoc_lookup where Company=:company and assignment_availability!=1 ");
            switch (type)
            {
                case "事件":
                    sql = sql.Append("and (support_organization='事件支持组' or support_organization='公用组') ");
                    break;
                case "变更":
                    sql = sql.Append("and (support_organization='变更支持组') ");
                    break;
                case "问题":
                    sql = sql.Append("and (support_organization='问题支持组' or support_organization='公用组') ");
                    break;
                default:
                    break;
            }
            sql = sql.Append(" order by Full_Name");
            //sql = sql.Append(" distinct Full_Name");
            using (OracleDataAccess conitsm = new OracleDataAccess(ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString))
            {
                return ModelHelper<SupportGroupCompany>.GetModel_By_DirectSql(sql.ToString(), new List<OracleParameter>()
                 {
                     new OracleParameter(){ ParameterName="company",OracleDbType=OracleDbType.NVarchar2,/*字符串类型必须有Size*/Size=50,Value=Company}
                 }, conitsm);
            }
        }


        public User_XModel GetUserByPeopleNo(string ID)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="PeopleNo", OracleDbColumnType=OracleDbType.NVarchar2,
                        OracleDbColumnValue=ID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            return ModelHelper<User_XModel>.GetModel_By_QueryParams(attParam, null, null, null, oda, null).FirstOrDefault();
        }
    }
}
